<?php
// +----------------------------------------------------------------------
// | Author: weifashi <605403358@qq.com>
// +----------------------------------------------------------------------
namespace app\admin\actions\controls;
use think\facade\Db;
use app\admin\actions\Methods;
use app\admin\actions\controls\SearchService;

class GridService {
	
	//基础定义
	private $page = 0;
	private $pageSize = 50;
	private $keyword = '';
	private $gridNode = '';
	private $filter = [];
	private $order = '';
		
	/**
    * 构造函数自动执行
    * @return null
    */
    public function __construct($xml='',$id=''){
		//错误级别
		error_reporting(E_ERROR | E_PARSE);
    	//TODO 获取参数
    	$param = request()->param();
    	list($page,$pageSize,$keyWord,$isRes,$xmlFile,$nodeId) = [
    		$param['pageIndex'] ? ($param['pageIndex']-1) : $this->page,
    		$param['pageSize'] == -1 ? 100000 : ($param['pageSize'] ?:  $this->pageSize ),
    		$param['keyWord'] ?: '',
    		$param['isRes'] ?: false,
    		$param['xmlFile'] ?: false,
    		$id ?: $param['id'] ?: '',
    	];
    	//TODO 分页参数
    	$this ->pageSize =  $pageSize;
    	$this ->page = $page > 0 ? $page * $pageSize : $page;
    	//TODO 基础参数
    	$this->keyword = $keyword = json_decode($keyWord,true);
    	$this->xmlFile = $xmlFile = $xmlFile ? $xmlFile : Methods::getXmlFile($keyword["xml"]?:$xml);
    	if(!$this->gridNode = self::isExistNode($xmlFile,$keyword["id"] ?: $nodeId)){
    		throw new \think\Exception('Grid-找不到xml对应节点，或xml文件格式错误！');
    	}
    	$this->filter = !empty($keyword["filter"]) ? $keyword["filter"] :  '' ;
		$this->order = !empty($keyword["order"]) ? $keyword["order"] :  '';
    }	

	 /**
     * 获取示例
     * @param array $options 实例配置
     * @return static
     */
    public static function instance($p1=null,$p2=null,$p3=null)
    {
        return new self($p1,$p2,$p3);
    }
    
	/**
    * 获取Grid选项配置
    * /admin/Api/Grid/getOption
    * @access public
    * @param string $id   			
    * @param string $xml 		
    * @return array
    */
	public function getOption(){
		$data = array();
	    if( $gridNode = $this->gridNode ){
			//整体配置
			$data = array_merge( $data, self::getXmlNodeAttrs($gridNode) );
			$data = array_merge( $data, self::getXmlNodeAttrs($gridNode -> sql) );
			$data = array_merge( $data, self::getXmlNodeAttrs($gridNode -> buttons) );
			$data = array_merge( $data, self::getXmlNodeAttrs($gridNode -> cells) );
            //搜索控件
			if($search = $gridNode -> search){
				$data["search"] = SearchService::instance($search)->getOption(); 
			}
			//按钮
			$data["btns"] = self::getButtons( $gridNode->buttons );
			//表头配置
			$data["cols"] = [ self::getCols( $gridNode->cells ) ];
			
	    }
		return $data;
	} 
	
	/**
    * 获取Grid数据
    * /admin/Api/Grid/getData
    * @access public
    * @param string 			$pageSize   			每页数据条数
    * @param string 			$pageIndex 		    指定第几页
    * @param jsonString   $keyWord 			过滤参数 {id:"",xml:"",filter:"",filter2:"",filter3:""}
    * @return array
    */
   
	public function getData($where=''){
		$data = array();
		$sql = $this->gridNode -> sql;
		$sqlAttrs =  self::getXmlNodeAttrs( $this->gridNode -> sql );
		
		//调用接口		
        if( $interface = $this->gridNode ->sql-> interface['url'].'' ){
        	return Methods::callApi($interface); 
        }
        
        //替换占位符
		$sql = Methods::replaceExp($sql);
		
		//转换过滤条件
		$this->filter && $sql = self::filterTransform($sql,$this->filter);
		
        //删除是否有权限
        if( $where && !Db::query($sql.' and '.$where)[0] ){
            return Db::query($sql.' and '.$where)[0] ? true : false;
        }
        
		//添加排序
		if($this->order){
			$sql .=" ".$this->order;
		}else if($orderBy = $sqlAttrs['order']){
			$sql .=" order by ".$orderBy;
		}
		
		//监听xmlsql语句是否有除了查询以外的动作
		if( !$sql = self::isExistDangerous($sql) ){
			throw new \think\Exception('监听到sql语句有查询以外的动作，请调整！');
		}
		
		//替换时间
		$sql = str_replace("%y-%m-%d","%Y-%m-%d",$sql);
		$sql = str_replace("%h:%i:%s","%H:%i:%s",$sql);
		
		if(($sqlAttrs['showSql'] ?? '') == "true"){
			throw new \think\Exception($sql.' limit '.$this ->page.','.$this ->pageSize);
		}
		
		return [
			"total" => Db::query(' select count(1) as total  from ('.$sql.') t ')[0]['total'],
			"data" => Db::query($sql.' limit '.$this ->page.','.$this ->pageSize)
		];
	}
	
	/**
    * 获取xml节点属性 建值对
	*  @return  array
    */
	private function getXmlNodeAttrs($node){
		$data = [];
		if($node){
			foreach($node->attributes() as $key => $attr ){
	    		$data["$key"]="$attr";
			}
		}
		return $data;
	}
	
	/**
    * 获取按钮配置
	*  @return  array
    */
	private function getButtons($buttons){
		$data = array();
		foreach($buttons->button ?: [] as $buttonsNode ){
			$btns = self::getXmlNodeAttrs( $buttonsNode );
			$btns["btns"]= array();
			foreach($buttonsNode -> button as $buttonNode ){
				array_push($btns["btns"], self::getXmlNodeAttrs( $buttonNode ) );
			}
			array_push($data, $btns);
		}
		return $data;
	}
	
	/**
    *  获取表头配置
	*  @return  array
    */
	private function getCols($cellsNode){
		$cols = array();
		foreach($cellsNode->cell ?: [] as $cell){
		  	if($cell){
			   	$data = self::getXmlNodeAttrs( $cell );
				$data['children'] = self::getCols( $cell ); 
				//下拉选项
			   	$data["options"] = array();
		  		$optionDataUrl = $cell-> attributes()["dataUrl"]; 
				if( $optionDataUrl && !empty($optionDataUrl.'') ){
				    $selectdata = Methods::callApi( $optionDataUrl );
				    if( !is_array($selectdata) ){ $selectdata = $selectdata->getData()['data']; }
					$data["options"] = $selectdata; 
				}else{
					foreach( $cell->option as $optionNode ){
    					$option =  self::getXmlNodeAttrs( $optionNode );
	    				$option["label"]="$optionNode";
	    				array_push( $data["options"], $option );
	    			}
				}
				//按钮
				$data["buttons"] = array();
				foreach( $cell->button as $buttonsNodes ){
					$btns = self::getXmlNodeAttrs( $buttonsNodes );
					$btns['btns'] = [];
					foreach( $buttonsNodes->button as $buttonsNode ){
						array_push( $btns["btns"], self::getXmlNodeAttrs( $buttonsNode ) );
					}
    				array_push( $data["buttons"],  $btns);
    			}
			   	$cols[] = $data;
		  	}
		}
		return $cols;
	}
	
	
	/**
    * 监听xmlsql语句是否有除了查询以外的动作
    * @access private
    * @return string
    */
	private function isExistDangerous($sql){
		$sql = strtolower($sql);
		$sql = preg_replace('/\binsert\s|\bupdate\s|\bdelete\s|\bcreate\s|\balter\s|\bdrop\s|(\bexec|\bexecute)[\s\(]|\bsp_|\bxp_|\sinto\s/','error--error',$sql);
		if(strstr($sql,"error--error")){
			return false;
		}else{
			return $sql;
		}
	}
	
	/**
    * 过滤条件转换
    * @access private
    * @param array $filter   过滤参数
    * @return array
    */
	private function filterTransform($sql,$filter){
		//替换 (1=1) (2=3)
		$filters = array();
		$array = array(" 1 = 1 ");
		foreach($filter as $v){
			$value = $v["value"];  $opera= $v["opera"]; $field = $v['field'];
			//逗号分割多个字段
			if( strstr($field,',') ){
				foreach( explode(",",$field) as $id ){
					$s1[] = $opera == 'like' ? "$id like '%".$value."%'" : "$id = '$value'";
				}
				$field =  '('.join(' or ', $s1).") and '1'"; $opera = '=';  $value = '1';
			}
			//时间类型
			$field = $v["type"] == 'date' ? ('date_format('.$field.',"%Y-%m-%d")') : $field;
			//占位符处理
			if(!strstr($field,"{") && !strstr($field,"}")){
				if($opera=="like"){
					array_push($array,"$field  like '%$value%' ");
				}else if($opera=="in"){
					$value = "'".str_replace(",","','","$value")."'";
					array_push($array,"$field in ($value) ");
				}else if($opera=="not in"){
					$value = "'".str_replace(",","','","$value")."'";
					array_push($array,"$field not in ($value) ");
				}else if(in_array("$opera", ['is','not is'])){
					array_push($array,"$field $opera $value");
				}else if( in_array("$opera", ["=","<=",">=","<",">","!=","<>",'lt','elt','gt','egt']) ){
                    if($opera == 'gt') $opera='>';
                    if($opera == 'egt') $opera='>=';
                    if($opera == 'lt') $opera='<';
                    if($opera == 'elt') $opera='<=';
                    array_push($array,"$field $opera '$value' ");
                }
			}else{
				$field = str_replace("{","",$field);
				$field = str_replace("}","",$field);
				$filters["$field"] = $value;
			}
		}
		$sql = str_replace("1=1", implode(" and ", $array), $sql);
		$sql = str_replace("2=3","10=10",$sql);
			
		// 替换 占位符
		// 1.先替换与filter标签和filter值匹配的value再次格式化的值
//		foreach($this->gridNode -> filter ->field ?: [] as  $node){
//			$attr = self::getXmlNodeAttrs( $node );
			foreach($filter as $field => $vals){
//				if($attr["name"] == $vals['field']){
					$val = $vals['value'];  $field = $vals['field'];
					$value = preg_replace("/{val}/","$val","{$val}");
					$in_value= preg_replace("/{val}/","'$val'", "'".str_replace(",","','","$value")."'");
					$sql = str_replace("({{{$field}}})","($in_value)",$sql);
					$sql = str_replace("{{{$field}}}","$value",$sql);
//				} 
			}
//		}
		//2.然后替换剩下的 与 $filter 匹配的 {{}}
		foreach($filters as $field => $v){
			$in_value= "'".str_replace(",","','","$v")."'";
			$sql = str_replace("({{{$field}}})","($in_value)",$sql);
			$sql = str_replace("{{{$field}}}","'$v'",$sql);
		}
		//3.再替换掉剩下的与filter标签匹配的 空值格式
		foreach($this->gridNode -> filter ->field ?: [] as $node){
			$attr = self::getXmlNodeAttrs( $node );
			$val = preg_replace("/{val}/","$vals",$attr["default"]);
			$sql = str_replace("{{$attr['name']}}","$val",$sql);
		}
		//4.替换掉所有不匹配的 {{}} 为 1=1
		$sql =  preg_replace("/{{.*?}}/","",$sql);
		//返回
		return $sql;
	}
	
	/**
    * 删除数据
    * /admin/Api/Grid/deleteData
    * @access public
    * @return array
    */
	public function deleteData($params){
		$sqlAttrs =  self::getXmlNodeAttrs( $this->gridNode-> sql );
		$entity = $sqlAttrs['entity'];  $key = $sqlAttrs['key'];
		if(!$entity) throw new \think\Exception('请配置entity属性！');
		if(!$key) throw new \think\Exception('请配置key属性！');
		$entitys =  explode(",",$entity); $keys =  explode(",",$key);
		foreach($params as $v){
			$val = $v[$keys[0]];
			$index=0;
			foreach($entitys as $entit){
				$keys["$index"] =  $keys["$index"] ? $keys["$index"] : $keys[0];
                $where = $keys["$index"].'="'.$val.'"';
                if( !$this->getData($where) ){
                    throw new \think\Exception('无权限！');
                }
				$db[] = Db::table($entit)->where($where)->delete();
				$index++;
			}
		}
		if($db[0]) return $params;
		throw new \think\Exception('删除失败,数据不存在或配置错误！');
	}
	
	/**
    * 调整排序
    * /admin/Api/Grid/sortAdjust
    * @access public
    * @return array
    */
	public function adjustSort($moveId,$coverId,$sortField){
		$sqlAttrs =  self::getXmlNodeAttrs( $this->gridNode-> sql);
		$entity = $sqlAttrs['entity']; $key = $sqlAttrs['key'];
		if(!$entity) throw new \think\Exception('请配置entity属性！');
		if(!$key) throw new \think\Exception('请配置key属性！');
		$entity =  explode(",",$entity)[0]; $key =  explode(",",$key)[0];	    
		try {
			$sort_no = Db::table($entity)->where($key,$coverId)->value("$sortField");
			$sort_no2 = Db::table($entity)->where($key,$moveId)->value("$sortField");
	    	Db::table($entity)->where($key,$moveId)->update([ "$sortField" => $sort_no ]);
			Db::table($entity)->where($key,$coverId)->update([ "$sortField"=> $sort_no2 ]);
		} catch (\Exception $e) {
			throw new \think\Exception('数据不存在或配置错误！');
		}
		return true;
	}
	
	/**
    * 更新字段
    * /admin/Api/Grid/updateField
    * @access public
    * @return array
    */
	public function updateField($ids,$name,$value){
		$sqlAttrs =  self::getXmlNodeAttrs( $this->gridNode -> sql );
		$entity = $sqlAttrs['entity'];  $key = $sqlAttrs['key'];
		if(!$entity) throw new \think\Exception('请配置entity属性！');
		if(!$key) throw new \think\Exception('请配置key属性！');
		$entity =  explode(",",$entity)[0];  $key =  explode(",",$key)[0];	     
		try {
		    
		    $where = '';
            foreach(explode(",", $ids) as $id){
               $where .=  !$where ? "$key=$id" : " and $key=$id"; 
            }
            if( !$this->getData($where) ){
                throw new \think\Exception('无权限！');
            }
            
	    	Db::table($entity)->where($key,'in',$ids)->update([ "$name" => $value ]);
		} catch (\Exception $e) {
			throw new \think\Exception( $e->getMessage() );
		}
		return true;
	}
	
	/**
    * 更新字段
    * /admin/Api/Grid/updateField
    * @access public
    * @return array
    */
	public function updateRow($ids,$data){
		$sqlAttrs =  self::getXmlNodeAttrs( $this->gridNode -> sql );
		$entity = $sqlAttrs['entity'];  $key = $sqlAttrs['key'];
		if(!$entity){ return -2; }   if(!$key){ return -3; }
		$entity =  explode(",",$entity)[0];  $key =  explode(",",$key)[0];	    
		try {
		    
            $where = '';
            foreach(explode(",", $ids) as $id){
               $where .=  !$where ? "$key=$id" : " and $key=$id"; 
            }
            if( !$this->getData($where) ){
                throw new \think\Exception('无权限！');
            }
            
			$data = json_decode($data,true);
			unset($data[$key]);
            
	    	Db::table($entity)->where($key,'in',$ids)->update($data);
		} catch (\Exception $e) {
			throw new \think\Exception( $e->getMessage() );
		}
		return true;
	}
	
	//<!--  = 导出  =  -->
	//<!--  = /Admin/Api/Grid/exportExcel =  -->
	public function getXlsData(){
		$data = array();
		$xlsHead  = self::getXlsHead( self::getAllField() );
		
        //Excel 数据
        $rows=array();
        foreach($xlsHead as $val){
        	if(!$val["children"]){
        		array_push($rows,''.$val['field']);
        	}else{
        		foreach($val["children"] as  $v){
        		 	if(!$v["children"]){
        		 		array_push($rows,''.$v['field']);
        		 	}else{
        		 		foreach($v["children"] as  $s){
		        		 	array_push($rows,''.$s['field']);
		        		}
        		 	}
        		}
        	}
        }
        
        $xlsData=array();
        $datas = self::getData();
        foreach($datas['data'] as $key=>$val){
        	foreach($rows as $k=>$row){
        		$xlsData[$key][$k]=''.$val["$row"];
        	}
        }
		
		$data['xlsHead'] = $xlsHead;
		$data['xlsName'] = $this->gridNode->attributes()['title'] ?? '列表数据';
        $data['xlsData'] = $xlsData;
		
		return $data; 
	}
	
	/**
    * 获取xsl头部
    * @access private
    * @param string $allField   	
    * @return array
    */
	private function getXlsHead($allField){ 
		$Head  = array();
		foreach($allField  as $val){
			$childrenCount = count( $val->cell );
			$arr=array(
				'field'=>$val['field'],
				'value'=>$val['title'],
				'width'=>20,
				'col'  => 1,
				'row'=> 1
			);
			//第二层
			if($childrenCount>0){
				$arr['children']=array();
				foreach($val ->cell as $val2){
					$childrenCount2 = count( $val2->cell);
					$arr2=array(
						'field'=>''.$val2['field'],
						'value'=>''.$val2['title'],
						'width'=>20,
						'col'  => 1,
						'row'=>1
					);
					//第三层
					if($childrenCount2>0){
						$arr2['children']=array();
						foreach($val2 ->cell as $val3){
							$childrenCount3 = count( $val3->cell);
							$arr3=array(
								'field'=>$val3['field'],
								'value'=>$val3['title'],
								'width'=>20,
								'col'  => 1,
								'row'=>1
							);
							array_push($arr2['children'],$arr3);
						}
					}
					array_push($arr['children'],$arr2);
				}
			}
			array_push($Head,$arr);
		}
	
		//获取头部层级
		$rowIndex=1;
        foreach($Head as $val){
        	if($val["children"]){
        		$rowIndex = 2;
        		foreach($val["children"] as $children){
        			if($children["children"]){
        				$rowIndex = 3;
        			}
        		}
        	}
        }
		
		//设置row
		foreach($Head as $key=>$val){
        	if(!$val["children"]){
        		$Head[$key]["row"] = $rowIndex;
        	}else{
        		$Head[$key]["row"] = $rowIndex - 1;
        		foreach($val["children"] as $v){
        			if($v["children"]){
        				$Head[$key]["row"] = $rowIndex - 2;
        			}
        		}
        	}
        }
        
        //设置col
		foreach($Head as $key=>$val){
        	if($val["children"]){
        		$row1 = count( $val["children"] );
        		$childrenCount=0;
        		foreach($val["children"] as $k =>$v ){
        			if($v["children"]){
        				$childrenCount = 1;
        				$Head[$key]["children"][$k]["col"] = count( $v["children"] );
        				$row1 += (count( $v["children"] ) - 1);
        			}
        		}
        		if($childrenCount>0){
        			foreach($val["children"] as $k =>$v ){
	        			if(!$v["children"]){
	        				$Head[$key]["children"][$k]["row"]=2;
	        			}
	        		}
        		}
        		$Head[$key]["col"] = $row1;
        	}
        }
        return $Head;
	}
	
	
	/**
    * 获取所有字段
    * @access private
    * @param string $id   	xmlid
    * @param array $xml 	xml路径
    * @return string
    */
	private function getAllField(){
		$allFields=array();
		foreach($this->gridNode -> cells as $cells){
			foreach($cells -> cell as $cell){
				array_push($allFields,$cell);
    		}
		}
	   	return $allFields;
	}
	
	/**
    * 是否存在grid节点
    * /admin/Api/Grid/getOption
    * @param string $xmlFile   			xml-id
    * @return  bu
    */
	private function isExistNode( $xmlFile,$id ){
		$node = false;
		foreach( $xmlFile->grid as $val){
	    	if( $id == $val->attributes()["id"] ){
	    		$node = $val;
	    	}
		}
		return $node;
	}
	
}




